高级特性

视图

当我们不想每次都重复键入常用的查询，就可以使用视图。

当我们创建一个视图，这会给查询起一个名字，这样就可以直接把视图当成表来使用：

sql
1CREATE VIEW myview AS SELECT city,temp_lo,temp_hi,prcp, date, location FROM weather,cities WHERE city = name;

sql
1SELECT * FROM myview;

外键

cities 表跟 weather 表之间是有关联关系的，现在我想要确保 weather 表插入数据之前，cities 表中有对应的数据，就可以使用外键。

重新创建两张表，并且将 weather 的 city 关联到 cities 表中的 city：

sql
1CREATE TABLE cities (
2        city     varchar(80) primary key,
3        location point
4);
5
6CREATE TABLE weather (
7        city      varchar(80) references cities(city),
8        temp_lo   int,
9        temp_hi   int,
10        prcp      real,
11        date      date
12);

此时在 cities 没有对应 city 的情况下，在 weather 中插入一条记录：

sql
1INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

Postgres 提示非法操作：

sql
1Query 1 ERROR: ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
2DETAIL:  Key (city)=(Berkeley) is not present in table "cities".

正确使用外键会提高数据库应用的质量。

事务

事务最重要的一点是它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作。

步骤之间的中间状态对于其他并发事务是不可见的，并且如果有某些错误发生导致事务不能完成，则其中任何一个步骤都不会对数据库造成影响。

有一个特别经典的转账例子：

一个保存着多个用户账单余额和支行总存款额的银行数据库。假设我们需要记录一笔从 A 账户到 B 账户的额度为 100 的转账，涉及到的 SQL 命令为：

sql
1-- 扣除 A 账户的余额
2UPDATE accounts SET balance = balance - 100.00
3    WHERE name = 'A';
4-- 扣除 A 所在的支行的存款余额
5UPDATE branches SET balance = balance - 100.00
6    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'A');
7-- 增加 B 账户的余额
8UPDATE accounts SET balance = balance + 100.00
9    WHERE name = 'B';
10-- 增加 B 所在的支行的存款余额
11UPDATE branches SET balance = balance + 100.00
12    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'B');

银行方面希望要么全部都发生，要么全部不发生，避免因为系统错误导致 B 收到 100 元而 A 没有被扣款的情况发生。

我们需要一种保障，当其中一种情况没有发生时则整个步骤都不会有任何效果。

将这些更新组织成一个事务就可以给我们这种保障。

同时我们希望能保证一旦一个事务被完成，它就能永久地记录下来即使发生崩溃也不会丢失。一个事务型数据库保证一个事务在被报告为完成之前它所做的所有更新都被记录在持久存储（即磁盘）。

当多个事务并发运行时，每一个都不能看到其他事务未完成的修改。例如，如果一个事务正忙着总计所有支行的余额，它不会只包括 Alice 的支行的扣款而不包括 Bob 的支行的存款。

所以事务的全做或全不做并不只体现在它们对数据库的持久影响，也体现在它们发生时的可见性。一个事务所做的更新在它完成之前对于其他事务是不可见的，而之后所有的更新将同时变得可见。

在 PostgreSQL 中，用 BEGIN 和 COMMIT 包裹 SQL 语句来开启事务：

sql
1BEGIN;
2UPDATE accounts SET balance = balance - 100.00
3    WHERE name = 'Alice';
4-- etc etc
5COMMIT;

如果，在事务执行中我们并不想提交（或许是我们注意到 Alice 的余额不足），我们可以发出ROLLBACK命令而不是COMMIT命令，这样所有目前的更新将会被取消。

PostgreSQL 实际上将每一个 SQL 语句都作为一个事务来执行。如果我们没有发出BEGIN命令，则每个独立的语句都会被加上一个隐式的BEGIN以及（如果成功）COMMIT来包围它。一组被BEGIN和COMMIT包围的语句也被称为一个事务块。

也可以利用保存点来以更细的粒度来控制一个事务中的语句。保存点允许我们有选择性地放弃事务的一部分而提交剩下的部分。在使用SAVEPOINT定义一个保存点后，我们可以在必要时利用ROLLBACK TO回滚到该保存点。该事务中位于保存点和回滚点之间的数据库修改都会被放弃，但是早于该保存点的修改则会被保存。

在回滚到保存点之后，它的定义依然存在，因此我们可以多次回滚到它。反过来，如果确定不再需要回滚到特定的保存点，它可以被释放以便系统释放一些资源。记住不管是释放保存点还是回滚到保存点都会释放定义在该保存点之后的所有其他保存点。

所有这些都发生在一个事务块内，因此这些对于其他数据库会话都不可见。当提交整个事务块时，被提交的动作将作为一个单元变得对其他会话可见，而被回滚的动作则永远不会变得可见。

假设我们从 A 账户扣款 100 美元，然后存款到 B 账户，结果直到最后发现我们应该存到 C 账户。我们可以使用保存点来做到：

sql
1BEGIN;
2UPDATE accounts SET balance = balance - 100.00
3    WHERE name = 'Alice';
4SAVEPOINT my_savepoint;
5UPDATE accounts SET balance = balance + 100.00
6    WHERE name = 'Bob';
7-- oops ... forget that and use Wally's account
8ROLLBACK TO my_savepoint;
9UPDATE accounts SET balance = balance + 100.00
10    WHERE name = 'Wally';
11COMMIT;

ROLLBACK TO是唯一的途径来重新控制一个由于错误被系统置为中断状态的事务块，而不是完全回滚它并重新启动。